package com.samson.empapp.dao; 

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.EmployeesDbo;
import com.samson.empapp.util.DbUtil;


public class EmployeesDao implements IEmployeesDao {

	static Logger logger = Logger.getLogger(EmployeesDao.class);
	
	public EmployeesDbo findById (int employeeId){  
	EmployeesDbo dbo = null; 
	Connection con = DbUtil.getOracleXEConn(); 
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	StringBuilder sb = null;
	try {
		sb = new StringBuilder();
		sb.append("SELECT "); 
		sb.append("EMPLOYEE_ID employeeId, FIRST_NAME firstName, LAST_NAME lastName, EMAIL email, PHONE_NUMBER phoneNumber, HIRE_DATE hireDate, JOB_ID jobId, SALARY salary, COMMISSION_PCT commissionPct, MANAGER_ID managerId, DEPARTMENT_ID departmentId "); 
		sb.append("FROM EMPLOYEES "); 
		sb.append("where EMPLOYEE_ID = "+ employeeId );
		pstmt = con.prepareStatement(sb.toString());
		
		logger.info("Executing Query " + sb.toString());
		rs = pstmt.executeQuery();
		if (rs.next()) {
			dbo = new EmployeesDbo();
			dbo.setEmployeeId(employeeId);
			dbo.setFirstName(rs.getString("firstName"));
			dbo.setLastName(rs.getString("lastName"));
			dbo.setEmail(rs.getString("email"));
			dbo.setPhoneNumber(rs.getString("phoneNumber"));
			dbo.setHireDate(rs.getDate("hireDate"));
			dbo.setJobId(rs.getString("jobId"));
			dbo.setSalary(rs.getInt("salary"));
			dbo.setCommissionPct(rs.getInt("commissionPct"));
			dbo.setManagerId(rs.getInt("managerId"));
			dbo.setDepartmentId(rs.getInt("departmentId"));
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		logger.error("Exception" + e);
	} finally {
		DbUtil.closeDBConnection(pstmt, rs, con, sb);
	}
	return dbo;
}
	
	public List<EmployeesDbo> findAllEmployees() { 
		List<EmployeesDbo> returnList = new ArrayList<EmployeesDbo>();
		EmployeesDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT "); 
			sb.append("EMPLOYEE_ID as employeeId, "); 
			sb.append("FIRST_NAME as firstName, "); 
			sb.append("LAST_NAME as lastName, "); 
			sb.append("EMAIL as email, "); 
			sb.append("PHONE_NUMBER as phoneNumber, "); 
			sb.append("HIRE_DATE as hireDate, "); 
			sb.append("JOB_ID as jobId, "); 
			sb.append("SALARY as salary, "); 
			sb.append("COMMISSION_PCT as commissionPct, "); 
			sb.append("MANAGER_ID as managerId, "); 
			sb.append("DEPARTMENT_ID as departmentId "); 
			sb.append("FROM EMPLOYEES ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery(sb.toString());
			while (rs.next()) {
				obj = new EmployeesDbo();
				obj.setEmployeeId(rs.getInt("employeeId"));
				obj.setFirstName(rs.getString("firstName"));
				obj.setLastName(rs.getString("lastName"));
				obj.setEmail(rs.getString("email"));
				obj.setPhoneNumber(rs.getString("phoneNumber"));
				obj.setHireDate(rs.getDate("hireDate"));
				obj.setJobId(rs.getString("jobId"));
				obj.setSalary(rs.getInt("salary"));
				obj.setCommissionPct(rs.getInt("commissionPct"));
				obj.setManagerId(rs.getInt("managerId"));
				obj.setDepartmentId(rs.getInt("departmentId"));
				
				returnList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}
		return returnList;
	}
	
	
	public void insert(EmployeesDbo dbo) throws Exception {
	      Connection con = DbUtil.getOracleXEConn();
			PreparedStatement pstmt = null;
			StringBuilder sb = null;
			try {
				sb = new StringBuilder();
				sb.append("INSERT INTO \"HR\".\"EMPLOYEES\" "); 
				sb.append("(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID) VALUES "); 
				sb.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ");
				pstmt = con.prepareStatement(sb.toString());
				logger.info("Executing Query " + sb.toString());
				pstmt.setInt(1, dbo.getEmployeeId());
				pstmt.setString(2, dbo.getFirstName());
				pstmt.setString(3, dbo.getLastName());
				pstmt.setString(4, dbo.getEmail());
				pstmt.setString(5, dbo.getPhoneNumber());
				pstmt.setDate(6, (Date) dbo.getHireDate());
				pstmt.setString(7, dbo.getJobId());
				pstmt.setInt(8, dbo.getSalary());
				pstmt.setInt(9, dbo.getCommissionPct());
				pstmt.setInt(10, dbo.getManagerId());
				pstmt.setInt(11, dbo.getDepartmentId());
				
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("Exception" + e);
				throw e;
			} finally {
				DbUtil.closeDBConnection(pstmt, con, sb);
			}
	}
	
	
}